package com.example.projectmanagement.repository;

import com.example.projectmanagement.model.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.Optional;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByUsername(String username);
    boolean existsByUsername(String username);
    
    Page<User> findByNameContaining(String name, Pageable pageable);
    Page<User> findByUsernameContaining(String username, Pageable pageable);
    Page<User> findByStatus(Integer status, Pageable pageable);
    
    @Query("SELECT COUNT(u) FROM User u WHERE u.studyPoints BETWEEN :min AND :max")
    Long countByStudyPointsBetween(@Param("min") Long min, @Param("max") Long max);
    
    Page<User> findByNameContainingAndUsernameContaining(String name, String username, Pageable pageable);
    Page<User> findByNameContainingAndStatus(String name, Integer status, Pageable pageable);
    Page<User> findByUsernameContainingAndStatus(String username, Integer status, Pageable pageable);
    
    Page<User> findByNameContainingAndUsernameContainingAndStatus(String name, String username, Integer status, Pageable pageable);
    
    // 查询非 ADMIN 和 LECTURER 角色的用户，按学习积分降序排列
    @Query("SELECT u FROM User u WHERE u.role NOT IN ('ADMIN', 'LECTURER') ORDER BY u.studyPoints DESC")
    Page<User> findAllStudentsOrderByStudyPointsDesc(Pageable pageable);
    
    // 根据关键字搜索非 ADMIN 和 LECTURER 角色的用户，按学习积分降序排列
    @Query("SELECT u FROM User u WHERE u.role NOT IN ('ADMIN', 'LECTURER') AND (u.name LIKE %:keyword% OR u.username LIKE %:keyword%) ORDER BY u.studyPoints DESC")
    Page<User> searchStudents(@Param("keyword") String keyword, Pageable pageable);

}